package server;

import java.util.ArrayList;
import java.util.List;
import java.sql.*;

/**
 * Database is a class that specifies the interface to the databases. Uses JDBC
 * and the MySQL Connector/J driver.
 */
public class Database {
	/**
	 * The database connection.
	 */
	private Connection conn;

	private PreparedStatement ps = null;

	private String findSalt = "SELECT salt FROM DBUsers WHERE name = ?";
	private String findUser = "SELECT * FROM DBUsers WHERE name = ? and pNbr = ? and password = ?";
	private String getUser = "SELECT * FROM DBUsers WHERE pNBR = ?";
	private String getJournal = "SELECT * FROM Journals WHERE patient_pnbr = ?";
	private String countJournals = "SELECT count(*) FROM Journals WHERE patient_pnbr = ?";
	private String updateLog = "INSERT into Log VALUES(null,?,?,?)";
	private String getLog = "SELECT * FROM Log ORDER by cts";

	/**
	 * Create the database interface object. Connection to the database is
	 * performed later.
	 */
	public Database() {
		conn = null;
	}

	/**
	 * Open a connection to the database, using the specified user name and
	 * password.
	 * 
	 * @param userName
	 *            The user name.
	 * @param password
	 *            The user's password.
	 * @return true if the connection succeeded, false if the supplied user name
	 *         and password were not recognized. Returns false also if the JDBC
	 *         driver isn't found.
	 */
	public boolean openConnection() {
		String userName = "db51";
		String password = "1010eda216";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://puccini.cs.lth.se/" + userName, userName,
					password);
		} catch (SQLException e) {
			System.out.println("SQLException i openConnection()");
			e.printStackTrace();
			return false;
		} catch (ClassNotFoundException e) {
			System.out.println("ClassNotFoundException i openConnection()");
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * Close the connection to the database.
	 */
	public void closeConnection() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			System.out.println("closeConnectionFail()");
		}
		conn = null;
	}

	/**
	 * Check if the connection to the database has been established
	 * 
	 * @return true if the connection has been established
	 */
	public boolean isConnected() {
		return conn != null;
	}
	
	
	
	/** Returns the salt stored in the database with the corresponding username/CN
	 * @param commonName
	 * @return salt if there's one associated with the commonname and null otherwise
	 */
	public String findSalt(String commonName) {
		ps = null;
		try {
			ps = conn.prepareStatement(findSalt);
			ps.setString(1, commonName);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getString("salt");
			}
		} catch (SQLException e) {
			System.out.println("findSaltFail ");
			e.printStackTrace();
		}
		return null;
	}

	/** Retrievs useful user info from the database
	 * @param pNbr the users person number
	 * @param password the users hashed password
	 * @param commonName the users username/CN
	 * @return String vector with the useful user info
	 */
	public String[] findUser(String pNbr, String password, String commonName) {
		ps = null;
		String[] data = new String[4];
		try {
			ps = conn.prepareStatement(findUser);
			ps.setString(1, commonName);
			ps.setString(2, pNbr);
			ps.setString(3, password);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				data[0] = rs.getString("name");
				data[1] = rs.getString("pNbr");
				data[2] = rs.getString("clearance");
				data[3] = rs.getString("division");
				return data;
			}
		} catch (SQLException e) {
			System.out.println("findUserFail ");
			e.printStackTrace();
		}
		return null;
	}
	
	/** Returns a String with information about a users account in the patient database.
	 * @param pNbr the person number of the queried user
	 * @return String with user info
	 */
	public String getUser(String pNbr) {
		ps = null;
		try {
			ps = conn.prepareStatement(getUser);
			ps.setString(1, pNbr);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getString("name") + " " + rs.getString("pNbr") + " "
						+ rs.getString("clearance") + " "
						+ rs.getString("division");
			}
		} catch (SQLException e) {
			System.out.println("getUserFail ");
			e.printStackTrace();
		}
		return null;
	}

	/** Returns all journals associated with a person with the specified person number
	 * @param pNbr the person number of the queried patient
	 * @return the users journal(s)
	 */
	public Journal[] getJournal(String pNbr) {
		ps = null;
		int count = 0;
		Journal[] journals = null;
		try {
			ps = conn.prepareStatement(countJournals);
			ps.setString(1, pNbr);
			ResultSet cs = ps.executeQuery();

			while (cs.next()) {
				count = cs.getInt("count(*)");
			}

			ps = null;

			if (count > 0) {
				journals = new Journal[count];
				
				ps = conn.prepareStatement(getJournal);
				ps.setString(1, pNbr);
				ResultSet rs = ps.executeQuery();

				int index = 0;

				while (rs.next() && count > 0) {
					journals[index] = new Journal(rs.getInt("journalNbr"),
							rs.getInt("division"),
							rs.getString("assNurse_pnbr"),
							rs.getString("assDoctor_pnbr"),
							rs.getString("patient_pnbr"));
					index++;
				}
			}
		} catch (SQLException e) {
			System.out.println("getJournalFail ");
			e.printStackTrace();
		}
		return journals;
	}

	/** Updates the log with the type of change made to a journal by a person.
	 * @param pNbr the person number of the user accessing a journal
	 * @param journal the number of the journal which has been viewed or modified
	 * @param change the type of change made, 'r' = read, 'm' = modified, 'd' = deleted, 'c' = created
	 */
	public void updateLog(String pNbr, int journal, String change) {
		ps = null;
		try {

			ps = conn.prepareStatement(updateLog);
			ps.setString(1, pNbr);
			ps.setInt(2, journal);
			ps.setString(3, change);
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println("updateLogFail");
			e.printStackTrace();
		}
	}

	/** Method for returning the database's log file
	 * @return a List<String> of the entire log
	 */
	public List<String> getLog() {
		List<String> logList = new ArrayList<String>();
		ps = null;
		try {
			ps = conn.prepareStatement(getLog);
			ResultSet rs = ps.executeQuery();
			String s1;
			String s2;
			String s3;
			String s4;
			logList.add("Date and time changed\tChanged by\tJournal\t\tType of change");
			while (rs.next()) {
				s1 = rs.getString("cts");
				s2 = rs.getString("pNbr");
				s3 = rs.getString("accessedJournal");
				s4 = rs.getString("change_made");
				logList.add(s1 + "\t" + s2 + "\t" + s3 + "\t\t" + s4);
			}
		} catch (SQLException e) {
			System.out.println("getLogFail");
			e.printStackTrace();
		}
		return logList;
	}
}
